﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Cat;
using System.Data.Odbc;
using System.Security.Cryptography;
using MyDBconnection;

namespace Thesis_2
{
    public partial class frmproduct : Form
    {
        ConnectionString cs = new ConnectionString();
        OdbcConnection con = null;
        OdbcCommand cmd = null;
        OdbcDataReader rdr = null;
        DatabaseConnection dbcon = new DatabaseConnection();
       
        public frmproduct()
        {
            this.Load += new EventHandler(frmproduct_Load);
            InitializeComponent();
            //tab 1 button
            btnNew.Click += new EventHandler(btnNew_Click);
            btnSave.Click += new EventHandler(btnSave_Click);
            btnView.Click += new EventHandler(btnView_Click);
            //Tab1 ComboBox
            cmbCategory.SelectedIndexChanged += new EventHandler(cmbCategory_SelectedIndexChanged);
            //Tab2 ComboBox
            cmbCatU.SelectedIndexChanged += new EventHandler(cmbCatU_SelectedIndexChanged);
            //tab2 button
            btnUpdate.Click+=new EventHandler(btnUpdate_Click);
            btnClear.Click += new EventHandler(btnClear_Click);
            //Tab2 text box
            txtsearch.TextChanged += new EventHandler(txtsearch_TextChanged);
            //Tab2 datagrid
            dataGridView1.RowHeaderMouseClick += new DataGridViewCellMouseEventHandler(dataGridView1_RowHeaderMouseClick);
        }
        void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            DataGridViewRow dr = dataGridView1.SelectedRows[0];
            txtProdU.Text = dr.Cells[0].Value.ToString();
            txtProdnameU.Text = dr.Cells[1].Value.ToString();
            cmbCatU.Text = dr.Cells[2].Value.ToString();
            cmbSubU.Text = dr.Cells[3].Value.ToString();
            cmbSuppU.Text = dr.Cells[4].Value.ToString();
        }
        void txtsearch_TextChanged(object sender, EventArgs e)
        {
            if (txtsearch.Text != "Search Product Name")
            {
                dbcon.dbconnection();
                dbcon.loadData("SELECT  * FROM tblproduct WHERE ProductName LIKE '%" + txtsearch.Text + "%'");
                dataGridView1.DataSource = dbcon.tbl;
                dbcon.DBClose();
            }
            else
            {
                GetData();
            }
        }
        //Tab2 Combo Box
        void cmbCatU_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                con = new OdbcConnection(cs.DBConn);

                con.Open();
                cmd = con.CreateCommand();

                cmd.CommandText = "SELECT * from tblcategory WHERE CategoryName = '" + cmbCatU.Text + "'";
                rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    textBox1.Text = rdr.GetInt32(0).ToString().Trim();
                }
                if ((rdr != null))
                {
                    rdr.Close();
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                cmbSubU.Items.Clear();
                cmbSubU.Text = "";
                cmbSubU.Enabled = true;
                cmbSubU.Focus();
                
                con = new OdbcConnection(cs.DBConn);
                con.Open();
                string ct = "Select distinct RTRIM(SubCName) from tblcategory,tblsubcategory where tblcategory.CategoryName=tblsubcategory.CategoryName and CategoryID= '" + textBox1.Text + "'";
                cmd = new OdbcCommand(ct);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    cmbSubU.Items.Add(rdr[0]);
                }
                con.Close();

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        //Tab2 Button Function
        void btnClear_Click(object sender, EventArgs e)
        {
            resetupdate();
        } 
        void btnUpdate_Click(object sender, EventArgs e)
        {
            if (txtProductName.Text == "")
            {
                MessageBox.Show("Please Enter Product ID", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtProductID.Focus();
                return;
            }
            if (cmbCategory.Text == "")
            {
                MessageBox.Show("Please Select Category to Update", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbCategory.Focus();
                return;
            }
            if (cmbsubCategory.Text == "")
            {
                MessageBox.Show("Please Select Sub-Category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbsubCategory.Focus();
                return;
            }
            if (cmbSupplier.Text == "")
            {
                MessageBox.Show("Please Select Supplier ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbSupplier.Focus();
                return;
            }
            dbcon.dbconnection();
            dbcon.UpdateData("Update tblproduct set ProductName='" + txtProductName.Text + "', CategoryName = '"+cmbCategory.Text+"' ,SubCname = '"+cmbsubCategory.Text+"', SupplierName = '"+cmbSupplier.Text+"' Where ProductID='" + txtProductID.Text + "';");
            MessageBox.Show("Update Success", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            reset();
            dbcon.DBClose(); 
        }
        //Tab1 combo box
        void cmbCategory_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                con = new OdbcConnection(cs.DBConn);

                con.Open();
                cmd = con.CreateCommand();

                cmd.CommandText = "SELECT * from tblcategory WHERE CategoryName = '" + cmbCategory.Text + "'";
                rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    textBox2.Text = rdr.GetInt32(0).ToString().Trim();
                }
                if ((rdr != null))
                {
                    rdr.Close();
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }
                
                cmbsubCategory.Items.Clear();
                cmbsubCategory.Text = "";
                cmbsubCategory.Enabled = true;
                cmbsubCategory.Focus();

                con = new OdbcConnection(cs.DBConn);
                con.Open();
                string ct = "Select distinct RTRIM(SubCName) from tblcategory,tblsubcategory where tblcategory.CategoryName=tblsubcategory.CategoryName and CategoryID= '" + textBox2.Text + "'";
                cmd = new OdbcCommand(ct);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    cmbsubCategory.Items.Add(rdr[0]);
                }
                con.Close();

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        //Tab1 Button Function
        void btnSave_Click(object sender, EventArgs e)
        {
            if (txtProductName.Text == "")
            {
                MessageBox.Show("Please enter Product Name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtProductName.Focus();
                return;
            }
            if (cmbCategory.Text == "")
            {
                MessageBox.Show("Please Select Category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbCategory.Focus();
                return;
            }
            if (cmbsubCategory.Text == "")
            {
                MessageBox.Show("Please Select Sub-Category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                cmbsubCategory.Focus();
                return;
            }
            if (cmbSupplier.Text == "")
            {
                MessageBox.Show("Please Select Supplier", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            
            }
            con = new OdbcConnection(cs.DBConn);
            con.Open();
            string ct = "Select ProductName from tblproduct where ProductName='" + txtProductName.Text + "'";
            cmd = new OdbcCommand(ct);
            cmd.Connection = con;
            rdr = cmd.ExecuteReader();

            if (rdr.Read())
            {
                MessageBox.Show("Product Name Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                txtProductName.Text = "";
                txtProductName.Focus();

                if ((rdr != null))
                {
                    rdr.Close();
                }
                return;
            }
            con.Close();

            dbcon.dbconnection();
            dbcon.AddData("Insert into tblproduct(ProductID,ProductName,CategoryName,SubCname,SupplierName)" + "VALUES ('" + txtProductID.Text + "','"+txtProductName.Text+"','"+cmbCategory.Text+"','"+cmbsubCategory.Text+"','"+cmbSupplier.Text+"')");
            dbcon.loadData("Insert into tblproductlogs(ProductID,User)" + "VALUES ('" + txtProductID.Text + "','" + lblUser.Text + "')");
            MessageBox.Show("Successfully added", "Product", MessageBoxButtons.OK, MessageBoxIcon.Information);
            reset();
            autoid();
            dbcon.DBClose();
        }  
        void btnNew_Click(object sender, EventArgs e)
        {
            reset();
            autoid();
        }
        void btnView_Click(object sender, EventArgs e)
        {
            frmProductList frmL = new frmProductList();
            frmL.Show();
            this.Enabled = false;
        }
        //Reset Value
        void resetupdate()
        {
            txtProdU.Text = null;
            txtProdnameU.Text = "";
            txtsearch.Focus();
            cmbSubU.Text = null;
            cmbSuppU.Text = null;
            cmbCatU.Text = null;
        }
        void reset()
        {
            txtProductID.Text = null;
            txtProductName.Text = "";
            txtProductName.Focus();
            cmbCategory.Text = null;
            cmbsubCategory.Text = null;
            cmbSupplier.Text = null;  
        }
        //Auto ID Generation
        void autoid()
        {
            txtProductID.Text = "PID-" + GetUniqueKey(5);

        }
        //Online User
        void userlog()
        {
            con = new OdbcConnection(cs.DBConn);
            con.Open();
            string us = "Select * from tbluserlog";
            cmd = new OdbcCommand(us);
            cmd.Connection = con;
            rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                lblUser.Text = (rdr["Username"].ToString());


            }

        }
        public void Fillsupplier()
        {
            try
            {
                con = new OdbcConnection(cs.DBConn);
                con.Open();
                string ct = "Select RTRIM(SupplierName) from tblsupplier order by SupplierName";
                cmd = new OdbcCommand(ct);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();

                while (rdr.Read())
                {
                    cmbSupplier.Items.Add(rdr[0]);
                    cmbSuppU.Items.Add(rdr[0]);
                }
                con.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public void FillCombo()
        {
            try
            {
                con = new OdbcConnection(cs.DBConn);
                con.Open();
                string ct = "Select RTRIM(CategoryName) from tblcategory order by CategoryName";
                cmd = new OdbcCommand(ct);
                cmd.Connection = con;
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    cmbCategory.Items.Add(rdr[0]);
                    cmbCatU.Items.Add(rdr[0]);  
                }
                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        public static string GetUniqueKey(int maxSize)
        {
            char[] chars = new char[62];
            chars = "123456789".ToCharArray();
            byte[] data = new byte[1];
            RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
            crypto.GetNonZeroBytes(data);
            data = new byte[maxSize];
            crypto.GetNonZeroBytes(data);
            StringBuilder result = new StringBuilder(maxSize);
            foreach (byte b in data)
            {
                result.Append(chars[b % (chars.Length)]);
            }
            return result.ToString();
        }
        public void GetData()
        {
            dbcon.dbconnection();
            dbcon.loadData("Select * from tblproduct");
            dataGridView1.DataSource = dbcon.tbl;
            dbcon.DBClose();

        }
        void frmproduct_Load(object sender, EventArgs e)
        {
            FillCombo();
            Fillsupplier();
            autoid();
            userlog();
            GetData();
            dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells;
            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
        }
    }

}
